
Declare @CarID bigint
Declare @CustomerID bigint

Declare @CallMeID bigint
Declare @ProviderCompanyID bigint
Declare @ProviderCompanyName nvarchar(120)
Declare @CallTime smallint
Declare @CallMeRequestDateTime datetime
Declare @PendingStatus bit

Declare @AgentLeadID bigint

Declare @OrderByOption int
Declare @MinPremium float
Declare @QuoteDateStart smallint
Declare @QuoteDateEnd smallint
Declare @CommenceDateStart smallint
Declare @CommenceDateEnd smallint

Declare @StartDate datetime
DEclare @EndDate datetime
Declare @StartCommDate date
Declare @EndCommDate date

Declare @strStartDate varchar(8)
DEclare @strEndDate varchar(8)

Select @MinPremium = MinPremium, 
				@QuoteDateStart = QuoteStart, 
				@QuoteDateEnd = QuoteEnd, 
				@CommenceDateStart = CommDateStart, 
				@CommenceDateEnd = CommDateEnd,
				@OrderByOption = OrderByOption
		From tblCXCRMLeadSetting Where [Status] = 1 And SettingID = 1

Declare @UsrTypeID int
Declare @AgentID int
Declare @AgentType varchar(3)
Declare @CallType smallint

Declare @IsNoNewLeadCallTime bit
Declare @NewCustomerCount int
Declare @AgentNewCallCount int
Declare @IsQuotaExceed bit
Declare @IsNoNewLead bit
Declare @IsENAgent bit
Declare @NoOfAgent int

Declare @CurrentCallTime smallint
Declare @CallMeCount int

Select @IsNoNewLeadCallTime = CASE
		WHEN CAST(GetDate() AS Time(0)) Between '00:00:01' AND '23:59:59' THEN 1
		ELSE 1
		END
		
Select @CurrentCallTime = CASE
		WHEN CAST(GetDate() AS Time(0)) Between '00:00:00' AND '12:00:00' THEN 1
		WHEN CAST(GetDate() AS Time(0)) Between '12:00:01' AND '16:00:00' THEN 2
		WHEN CAST(GetDate() AS Time(0)) Between '16:00:01' AND '23:59:59' THEN 3
		END
		
SET @UsrTypeID = 400
SET @AgentType = 'ONS'
SET @IsENAgent = 0
SET @IsNoNewLead = 0
SET @AgentID = 8543
SET @CallType = 100

Select @EndCommDate = GETDATE() + @CommenceDateEnd
Select @StartCommDate = GETDATE() + @CommenceDateStart
Select @StartDate = GETDATE() - @QuoteDateStart
Select @EndDate = GETDATE() - @QuoteDateEnd

Select @strStartDate = CONVERT(varchar(8), @StartDate, 112)
Select @strEndDate = CONVERT(varchar(8), @EndDate, 112)

Select @CurrentCallTime CurrentCallTime,@StartCommDate StartCommDate, @EndCommDate EndCommDate, @StartDate StartDate, @EndDate EndDate

	IF(@CallType <> 100)
	BEGIN
		Select 'First', u.CreatedDate, l.AgentLeadID, l.CreatedDate from tblCXCRMAgentLeads l
		INNER JOIN tblCEUser u ON u.CEID = l.CustomerID		
		Where u.UsrTypeId = @UsrTypeID AND l.[Status] = 1 And l.AgentID =@AgentID And l.CallType = @CallType 
		Order By l.CreatedDate ASC
	END
	ELSE
	BEGIN
		--CHECK FOR ALL PENDING (REMOVED @CallType) 
		--AND CAR ID and Customer ID must *NOT* be null or zero
		Select 'Second', c.CreatedDate, l.AgentLeadID, l.CreatedDate 
		from tblCXCRMAgentLeads l
		INNER JOIN tblCarDetailTH c ON l.CarID = c.CarID And l.CustomerID = c.RefCEUserID
		INNER JOIN tblCarPolicy cp ON l.CarID = cp.CECarID And l.CustomerID = cp.RefCEUserID
		INNER JOIN tblCEUser u ON u.CEID = cp.RefCEUserID		
		Where u.UsrTypeId = @UsrTypeID AND  l.[Status] = 1 And l.AgentID =@AgentID And l.CarID > 0 And l.CustomerID > 0
		--Order By CreatedDate DESC -- COMMENT BY PHYO >> FEEDING BACK DATE LEAD FROM 2012 Jan 12 to 2012 Jan 19
		--Order By CreatedDate ASC -- Sakthi Changed on 07 Nov 2012
		ORDER BY
		-- 1 AND 5
		CASE	WHEN @OrderByOption = 1 THEN c.CreatedDate
				WHEN @OrderByOption = 5 THEN cp.CoverageCommenceDate
				END ASC,
		CASE	WHEN @OrderByOption = 1 THEN cp.CoverageCommenceDate
				WHEN @OrderByOption = 5 THEN c.CreatedDate
				END ASC,
		-- 2 AND 6
		CASE	WHEN @OrderByOption = 2 THEN c.CreatedDate
				WHEN @OrderByOption = 6 THEN cp.CoverageCommenceDate
				END ASC,
		CASE	WHEN @OrderByOption = 2 THEN cp.CoverageCommenceDate
				WHEN @OrderByOption = 6 THEN c.CreatedDate
				END DESC,
		-- 3 AND 7
		CASE	WHEN @OrderByOption = 3 THEN c.CreatedDate
				WHEN @OrderByOption = 7 THEN cp.CoverageCommenceDate
				END DESC,
		CASE	WHEN @OrderByOption = 3 THEN cp.CoverageCommenceDate
				WHEN @OrderByOption = 7 THEN c.CreatedDate
				END ASC,
		-- 4 AND 8
		CASE	WHEN @OrderByOption = 4 THEN c.CreatedDate
				WHEN @OrderByOption = 8 THEN cp.CoverageCommenceDate
				END DESC,
		CASE	WHEN @OrderByOption = 4 THEN cp.CoverageCommenceDate
				WHEN @OrderByOption = 8 THEN c.CreatedDate
				END DESC,
		--=============BANK Sorting==========================
		CASE	WHEN @OrderByOption = 1003 THEN c.NInfo1 END DESC,
		CASE	WHEN @OrderByOption = 1003 THEN cp.CoverageCommenceDate END ASC
	END